﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MyKTVUtils;
using System.Data;
using FrmMain;
using System.Data.SqlClient;
using MyKTVUtils;


namespace MyKTVService
{
    public class SingerService
    {
        Dao dao = new Dao();
        public DataTable BindCboData()
        {

            DataTable table = dao.GetTable(StaticParam.GetSingerTypeTableString,null);
            DataRow row = table.NewRow();
            row["singertype_name"] = "请选择";
            row["singertype_id"] = 0;
            table.Rows.InsertAt(row, 0);
            return table;
        }

        public int AddSinger(string name, int singerTypeId, string sex, string photoUrl, string singerInfo)
        {
            string sql = string.Format(@"insert into singer values(@name,@singerTypeId,@sex,@photoUrl,@singerInfo)");
            SqlParameter[] parame =
            {   new SqlParameter("@name", name),
                new SqlParameter("@singerTypeId", singerTypeId),
                new SqlParameter("@sex", sex),
                new SqlParameter("@photoUrl", photoUrl),
                new SqlParameter("@singerInfo", singerInfo)
            };
            int result = dao.DoNonquery(sql, parame);
            return result;
        }

        public DataTable SelectSinger(string name, int typeId)
        {
             
            //获得查询后表的数据
            DataTable table = DataSetUtiles.getTableByTableName(TableNameEnum.SingerList);
            //如果表中没有数据，则清空查询后的表，并将新查询后的表的数据添加到新表
            if (table == null)
            {
                //绑定新的表
                table = RelodSingerList();
            }
            //  DataView条件筛选
            DataView view = new DataView(table);
            string  file = "1=1";
            if (!StringUtils.IsBlank(name)) {
                file += " and name like ('%" + name + "%')";
            }
            if (typeId != 0) {
                file += "and typeid =" + typeId;
            }
            view.RowFilter=(file);
         
            return table;
        }
        public DataTable SelectSinger(string name, int typeId, int pageIndex, int pageSize, ref int totalCount)
        {

            //获得查询后表的数据
            DataTable table = DataSetUtiles.getTableByTableName(TableNameEnum.SingerList);
            //如果表中没有数据，则清空查询后的表，并将新查询后的表的数据添加到新表
            if (table == null)
            {
                //绑定新的表
                table = RelodSingerList();
            }
            //  DataView条件筛选
            DataView view = new DataView(table);
            string file = " 1=1 ";
            if (!StringUtils.IsBlank(name))
            {
                file += " and name like ('%" + name + "%')";
            }
            if (typeId != 0)
            {
                file += " and typeid =" + typeId;
            }
            view.RowFilter = (file);
            totalCount = view.Count;
            DataTable table1 = view.ToTable();
             DataTable table2 = view.ToTable();
             table2.Rows.Clear();
            for (int i = 0; i < table1.Rows.Count; i++)
             {
                 if (i >= (pageIndex - 1) * pageSize && i < (pageIndex * pageSize))
                {
                    DataRow row = table2.NewRow();
                    DataRow newRow = table1.Rows[i];
                    row["r"] = newRow["r"];
                    row["id"] = newRow["id"];
                    row["name"] = newRow["name"];
                    row["sex"] = newRow["sex"];
                    row["typename"] = newRow["typename"];
                    row["url"] = newRow["url"];
                    row["info"] = newRow["info"];
                    table2.Rows.Add(row);
                }
             }
            return table2;
        }
        private DataTable forTable(DataTable table)
        {
            return new DataTable();
        }

        public int index=0;
        public DataTable RelodSingerList()
        {
            //清除表
            DataSetUtiles.RemoveDataTable(TableNameEnum.SingerList);
            string sql = string.Format(@"  select ROW_NUMBER() over(order by singer_id) as r,singer_id as id
  ,singer_name as name, singer_sex as sex ,singer.singertype_id as typeid, singertype_name as typename , singer_photo_url as url,
  singer_info as info
   from singer inner join singer_type on singer.singertype_id=singer_type.singertype_id ");

            
              DataTable table = dao.GetTable(sql, null);
            //将新查询表添加查询表

            DataSetUtiles.AddTable(table,TableNameEnum.SingerList);
            return table;
        }

        public DataView SingerTable()
        {
            string sql = string.Format(@"  select *  from  singerList ");
            DataTable table = dao.GetTable(sql, null);
            DataSetUtiles.AddTable(table, TableNameEnum.SingerList);
            DataView view2 = new DataView(table);
            string  fillString = " a >5*" + index;
         
            fillString += " and a <=5*(1+" + index + ")";
            view2.RowFilter = fillString;
            return view2;
        }
     
    }
}
